package easytasks.data;

import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import easytasks.settings.Settings;
import android.content.ContentValues;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DataManager
{
	private DataSetup dataSetup;
	private SharedPreferences sharedPref;
	
	private Date today;
	private Date tomorrow;
	private Date dayAfterTomorrow;
	private Date firstDayOfNextWeek;
	private Date firstDayOfSecondNextWeek;
	private Date minDay;
	private Date maxDay;
	
	public DataManager(DataSetup dataSetup, SharedPreferences sharedPref)
	{
		if (dataSetup == null) throw new IllegalArgumentException("'dataSetup' must be not null");
		if (sharedPref == null) throw new IllegalArgumentException("'sharedPref' must be not null");
		
		this.dataSetup = dataSetup;
		this.sharedPref = sharedPref;
	}
	
	private void refreshDates()
	{
		Calendar calendar = Calendar.getInstance();
		calendar.clear(Calendar.HOUR);
		calendar.clear(Calendar.HOUR_OF_DAY);
		calendar.clear(Calendar.MINUTE);
		calendar.clear(Calendar.SECOND);
		calendar.clear(Calendar.MILLISECOND);
		today = calendar.getTime();
		
		calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + 1); // January 32 would be February 1
		tomorrow = calendar.getTime();
		
		calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + 1);
		dayAfterTomorrow = calendar.getTime();
		
		calendar.setTime(today);
		
		String firstDay = sharedPref.getString(Settings.FIRST_DAY_OF_WEEK_KEY, Settings.FIRST_DAY_OF_WEEK_DEFAULT_VALUE);
		int firstDayOfWeek;
		
		if (firstDay.equals("mon")) firstDayOfWeek = Calendar.MONDAY;
		else if (firstDay.equals("tue")) firstDayOfWeek = Calendar.TUESDAY;
		else if (firstDay.equals("wed")) firstDayOfWeek = Calendar.WEDNESDAY;
		else if (firstDay.equals("thu")) firstDayOfWeek = Calendar.THURSDAY;
		else if (firstDay.equals("fri")) firstDayOfWeek = Calendar.FRIDAY;
		else if (firstDay.equals("sat")) firstDayOfWeek = Calendar.SATURDAY;
		else firstDayOfWeek = Calendar.SUNDAY;
		
		int diff = calendar.get(Calendar.DAY_OF_WEEK) - firstDayOfWeek;
		int sum;
		
		if (diff < 0) sum = - diff;
		else sum = - diff + 7;
		
		calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + sum);
		firstDayOfNextWeek = calendar.getTime();
		
		calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + 7);
		firstDayOfSecondNextWeek = calendar.getTime();
		
		minDay = new Date(0);
		maxDay = new Date(Long.MAX_VALUE);
	}
	
	private void prepareDb(SQLiteDatabase db)
	{
		String initialSql = "PRAGMA foreign_keys = ON;";
		db.execSQL(initialSql);
	}
	
	public int getTaskCategoryCount()
	{
		refreshDates();
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "tasks.time >= " + today.getTime();
		Cursor result = db.query("tasks, task_category_relations", new String[]{"count(distinct task_category_relations.category_id)"}, whereClause, null, null, null, null, null);
		result.moveToFirst();
		
		db.close();
		return result.getInt(0);
	}
	
	public List<TaskCategory> getTaskCategories()
	{
		refreshDates();
		List<TaskCategory> categories = new LinkedList<TaskCategory>();
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "tasks.id = task_category_relations.task_id and task_categories.id = task_category_relations.category_id and tasks.time >= " + today.getTime();
		Cursor result = db.query(true, "tasks, task_category_relations, task_categories", new String[]{"task_categories.id", "task_categories.name"}, whereClause, null, null, null, "name", null);
		
		while (result.moveToNext())
		{
			String id = result.getString(0);
			String name = result.getString(1);
			categories.add(new TaskCategory(id, name));
		}
		
		db.close();
		return categories;
	}
	
	// Returns null if task category does not exist	
	public TaskCategory getTaskCategoryById(String id)
	{
		if (id == null || id.equals("")) throw new IllegalArgumentException("'id' must be not null and not empty.");
		
		TaskCategory category = null;
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		Cursor result = db.query(true, "task_categories", null, "id = '" + id + "'", null, null, null, null, null);
		
		if (result.moveToNext())
		{
			String name = result.getString(1);
			category = new TaskCategory(id, name);
		}
		
		db.close();	
		return category;
	}
	
	// Returns null if task category does not exist	
	public TaskCategory getTaskCategoryByName(String name)
	{
		if (name == null || name.equals("")) throw new IllegalArgumentException("'name' must be not null and not empty.");
			
		TaskCategory category = null;
			
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		Cursor result = db.query(true, "task_categories", null, "name = '" + name + "'", null, null, null, null, null);
			
		if (result.moveToNext())
		{
			String id = result.getString(0);
			category = new TaskCategory(id, name);
		}
			
		db.close();	
		return category;
	}
	
	public List<TaskCategory> getAllTaskCategories()
	{
		refreshDates();
		List<TaskCategory> categories = new LinkedList<TaskCategory>();
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		Cursor result = db.query("task_categories", new String[]{"task_categories.id", "task_categories.name"}, null, null, null, null, "name", null);
		
		while (result.moveToNext())
		{
			String id = result.getString(0);
			String name = result.getString(1);
			categories.add(new TaskCategory(id, name));
		}
		
		db.close();
		return categories;
	}

	public List<TaskCategory> getCategoriesFromTask(Task task)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null");
		return getCategoriesFromTask(task.getId());
	}
	
	public List<TaskCategory> getCategoriesFromTask(String taskId)
	{
		if (taskId == null || taskId.equals("")) throw new IllegalArgumentException("'taskId' must be not null and not empty.");
	
		List<TaskCategory> categories = new LinkedList<TaskCategory>();
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "task_category_relations.task_id = '" + taskId + "' and task_category_relations.category_id = task_categories.id";
		Cursor result = db.query("task_categories, task_category_relations", new String[]{"task_categories.id", "task_categories.name"}, whereClause, null, null, null, "task_categories.name", null);
		
		while (result.moveToNext())
		{
			String id = result.getString(0);
			String name = result.getString(1);
			categories.add(new TaskCategory(id, name));
		}
		
		db.close();
		return categories;
	}
	
	// Returns task category ID
	public String addTaskCategory(TaskCategory category)
	{
		if (category == null) throw new IllegalArgumentException("'category' must be not null");
		
		String id = UUID.randomUUID().toString();
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		ContentValues values = new ContentValues(2);
		values.put("id", id); // Category's ID must be a random UUID (Universally Unique IDentifier)
		values.put("name", category.getName());
		
		db.insert("task_categories", null, values);
		db.close();
		
		return id;
	}
	
	public void addTaskCategoryRelation(Task task, TaskCategory category)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null");
		if (category == null) throw new IllegalArgumentException("'category' must be not null");
		
		addTaskCategoryRelation(task.getId(), category.getId());
	}
	
	public void addTaskCategoryRelation(String taskId, String categoryId)
	{
		if (taskId == null || taskId.trim().equals("")) throw new IllegalArgumentException("Task ID not defined.");
		if (categoryId == null || categoryId.trim().equals("")) throw new IllegalArgumentException("Task category ID not defined.");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		ContentValues values = new ContentValues(2);
		values.put("task_id", taskId);
		values.put("category_id", categoryId);
		
		db.insert("task_category_relations", null, values);
		db.close();
	}
	
	public void updateTaskCategory(TaskCategory oldValue, TaskCategory newValue)
	{
		if (oldValue == null) throw new IllegalArgumentException("'oldValue' must be not null");
		if (newValue == null) throw new IllegalArgumentException("'newValue' must be not null");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		ContentValues values = new ContentValues(4);
		values.put("name", newValue.getName());
		
		db.update("task_categories", values, "id = '" + oldValue.getId() + "'", null);
		db.close();
	}
	
	public void removeTaskCategoryRelation(Task task, TaskCategory category)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null.");
		if (category == null) throw new IllegalArgumentException("'category' must be not null.");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		String whereClause = "task_id = '" + task.getId() + "' and category_id = '" + category.getId() + "'";
		
		db.delete("task_category_relations", whereClause, null);
		db.close();
	}
	
	public void removeTaskCategoryRelationsByTask(Task task)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null.");
		
		String id = task.getId();
		if (id == null || id.trim().equals("")) throw new IllegalArgumentException("Task ID not defined.");
		
		removeTaskCategoryRelationsByTaskId(id);
	}
	
	public void removeTaskCategoryRelationsByTaskId(String taskId)
	{
		if (taskId == null || taskId.trim().equals("")) throw new IllegalArgumentException("'taskId' must be not null and not empty.");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		String whereClause = "task_id = '" + taskId + "'";
		
		db.delete("task_category_relations", whereClause, null);
		db.close();
	}
	
	public void removeTaskCategory(TaskCategory category)
	{
		if (category == null) throw new IllegalArgumentException("'category' must be not null");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		db.delete("task_categories", "id = '" + category.getId() + "'", null);
		db.close();
	}
	
	public int getTodayPendingTaskCount()
	{
		refreshDates();		
		return getPendingTaskBetweenDatesCount(today, tomorrow);
	}
	
	public int getTomorrowPendingTaskCount()
	{
		refreshDates();
		return getPendingTaskBetweenDatesCount(tomorrow, dayAfterTomorrow);
	}
	
	public int getCurrentWeekPendingTaskCount()
	{
		refreshDates();
		return getPendingTaskBetweenDatesCount(today, firstDayOfNextWeek);
	}
	
	public int getNextWeekPendingTaskCount()
	{
		refreshDates();
		return getPendingTaskBetweenDatesCount(firstDayOfNextWeek, firstDayOfSecondNextWeek);
	}
	
	public int getPendingTaskCount()
	{
		refreshDates();		
		return getPendingTaskBetweenDatesCount(today, maxDay);
	}
	
	public int getOldPendingTaskCount()
	{
		refreshDates();		
		return getPendingTaskBetweenDatesCount(minDay, today);
	} 
	
	public int getPendingTaskBetweenDatesCount(Date start, Date end)
	{
		if (start == null) throw new IllegalArgumentException("'start' must be not null.");
		if (end == null) throw new IllegalArgumentException("'end' must be not null.");
		if (start.compareTo(end) >= 0) throw new IllegalArgumentException("'start' must be earlier than 'end'.");
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "time >= " + start.getTime() + " and time < " + end.getTime() + " and done = 0";
		Cursor result = db.query("tasks", new String[]{"count(*)"}, whereClause, null, null, null, null, null);
		result.moveToFirst();
		
		db.close();
		// test
		int a = result.getInt(0);
		return a;
		//return result.getInt(0);
	}
	
	public int getPendingTaskByCategoryCount(TaskCategory category)
	{
		if (category == null) throw new IllegalArgumentException("'category' must be not null.");
		
		refreshDates();
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "tasks.id = task_category_relations.task_id and task_category_relations.category_id = task_categories.id and task_categories.id = '" + category.getId() + "' and tasks.time >= " + today.getTime() + " and tasks.done = 0";
		Cursor result = db.query("tasks, task_category_relations, task_categories", new String[]{"count(*)"}, whereClause, null, null, null, null, null);
		result.moveToFirst();
		
		db.close();
		return result.getInt(0);
	}
	
	public List<Task> getTodayTasks()
	{
		refreshDates();
		return getTasksBetweenDates(today, tomorrow);
	}
	
	public List<Task> getTomorrowTasks()
	{
		refreshDates();
		return getTasksBetweenDates(tomorrow, dayAfterTomorrow);
	}
	
	public List<Task> getCurrentWeekTasks()
	{
		refreshDates();
		return getTasksBetweenDates(today, firstDayOfNextWeek);
	}
	
	public List<Task> getNextWeekTasks()
	{
		refreshDates();
		return getTasksBetweenDates(firstDayOfNextWeek, firstDayOfSecondNextWeek);
	}
	
	public List<Task> getTasksByCategory(TaskCategory category)
	{
		if (category == null) throw new IllegalArgumentException("'category' must be not null.");
		
		List<Task> tasks = new LinkedList<Task>();
		
		refreshDates();
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "tasks.id = task_category_relations.task_id and task_category_relations.category_id = '" + category.getId() + "' and tasks.time >= " + today.getTime();
		String[] columns = {"tasks.id", "tasks.title", "tasks.time", "tasks.ignore_hour", "tasks.description", "tasks.done"};
		Cursor result = db.query(true, "tasks, task_category_relations", columns, whereClause, null, null, null, "time", null);
		
		while (result.moveToNext())
		{
			String id = result.getString(0);
			String title = result.getString(1);
			Date time = new Date(result.getLong(2));
			boolean ignoreHour = (result.getInt(3) != 0);
			String description = result.getString(4);
			List<TaskCategory> categories = getCategoriesFromTask(id);
			boolean done = (result.getInt(5) != 0);
			
			tasks.add(new Task(id, title, time, ignoreHour, description, categories, done));
		}
		
		db.close();
		return tasks;
	}
	
	public List<Task> getTasks()
	{
		refreshDates();
		return getTasksBetweenDates(today, maxDay);
	}
	
	public List<Task> getOldTasks()
	{
		refreshDates();
		return getTasksBetweenDates(minDay, today);
	}
	
	public Task getTaskById(String id)
	{
		if (id == null || id.equals("")) throw new IllegalArgumentException("'id' must be not null and not empty.");
		
		Task task = null;
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		Cursor result = db.query(true, "tasks", new String[]{"title", "time", "ignore_hour", "description", "done"}, "id = '" + id + "'", null, null, null, null, null);
		
		if (result.moveToNext())
		{
			String title = result.getString(0);
			Date time = new Date(result.getLong(1));
			boolean ignoreHour = (result.getInt(2) != 0);
			String description = result.getString(3);
			List<TaskCategory> categories = getCategoriesFromTask(id);
			boolean done = (result.getInt(4) != 0);
			
			task = new Task(id, title, time, ignoreHour, description, categories, done);
		}
		
		db.close();	
		return task;
	}
	
	public List<Task> getTasksBetweenDates(Date start, Date end)
	{
		if (start == null) throw new IllegalArgumentException("'start' must be not null.");
		if (end == null) throw new IllegalArgumentException("'end' must be not null.");
		if (start.compareTo(end) >= 0) throw new IllegalArgumentException("'start' must be earlier than 'end'.");
		
		List<Task> tasks = new LinkedList<Task>();
		
		SQLiteDatabase db = dataSetup.getReadableDatabase();
		String whereClause = "time >= " + start.getTime() + " and time < " + end.getTime();
		Cursor result = db.query("tasks", null, whereClause, null, null, null, "time", null);
		
		while (result.moveToNext())
		{
			String id = result.getString(0);
			String title = result.getString(1);
			Date time = new Date(result.getLong(2));
			boolean ignoreHour = (result.getInt(3) != 0);
			String description = result.getString(4);
			List<TaskCategory> categories = getCategoriesFromTask(id);
			boolean done = (result.getInt(5) != 0);
			
			tasks.add(new Task(id, title, time, ignoreHour, description, categories, done));
		}
		
		db.close();
		return tasks;
	}
	
	// Returns task ID
	public String addTask(Task task)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null");
		
		String id = UUID.randomUUID().toString();
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		ContentValues values = new ContentValues(5);
		values.put("id", id); // Task's ID must be a random UUID (Universally Unique IDentifier)
		values.put("title", task.getTitle());
		values.put("time", task.getTime().getTime());
		values.put("ignore_hour", task.getIgnoreHour() ? 1 : 0); // Convert a boolean to an integer
		values.put("description", task.getDescription());
		values.put("done", task.isDone() ? 1 : 0);
		
		db.insert("tasks", null, values);
		db.close();
		
		for (TaskCategory category : task.getCategories())
		{
			String categoryId = category.getId();
			
			if (categoryId == null || categoryId.equals(""))
			{
				TaskCategory savedCategory = getTaskCategoryByName(category.getName());
				
				if (savedCategory == null)
				{
					categoryId = addTaskCategory(category);
				}
				else
				{
					categoryId = savedCategory.getId();
				}
			}
			
			addTaskCategoryRelation(id, categoryId);
		}
		
		return id;
	}
	
	public void updateTask(Task oldValue, Task newValue)
	{
		if (oldValue == null) throw new IllegalArgumentException("'oldValue' must be not null");
		if (newValue == null) throw new IllegalArgumentException("'newValue' must be not null");
		
		String oldTaskId = oldValue.getId();
		if (oldTaskId == null || oldTaskId.equals("")) throw new IllegalArgumentException("Old task ID not defined.");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		ContentValues values = new ContentValues(4);
		values.put("title", newValue.getTitle());
		values.put("time", newValue.getTime().getTime());
		values.put("ignore_hour", newValue.getIgnoreHour() ? 1 : 0); // Convert a boolean to an integer
		values.put("description", newValue.getDescription());
		values.put("done", newValue.isDone() ? 1 : 0);
		
		db.update("tasks", values, "id = '" + oldTaskId + "'", null);
		db.close();
		
		// Clean all category relations...
		removeTaskCategoryRelationsByTaskId(oldTaskId);
			
		// ...and add new category relations
		for (TaskCategory category : newValue.getCategories())
		{
			String categoryId = category.getId();
			
			if (categoryId == null || categoryId.equals(""))
			{
				TaskCategory savedCategory = getTaskCategoryByName(category.getName());
				
				if (savedCategory == null)
				{
					categoryId = addTaskCategory(category);
				}
				else
				{
					categoryId = savedCategory.getId();
				}
			}
					
			addTaskCategoryRelation(oldTaskId, categoryId);
		}
	}
	
	public void markTaskAsDone(Task task)
	{
		markTask(task, true);
	}
	
	public void markTaskAsUnDone(Task task)
	{
		markTask(task, false);
	}
	
	public void markTask(Task task, boolean done)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		ContentValues values = new ContentValues(1);
		values.put("done", done ? 1 : 0);
		
		db.update("tasks", values, "id = '" + task.getId() + "'", null);
		db.close();
	}
	
	public void removeTask(Task task)
	{
		if (task == null) throw new IllegalArgumentException("'task' must be not null");
		
		SQLiteDatabase db = dataSetup.getWritableDatabase();
		prepareDb(db);
		
		db.delete("tasks", "id = '" + task.getId() + "'", null);
		db.close();
	}
}
